package com.api.sql;


import org.springframework.beans.factory.annotation.Value;

import java.io.File;
import java.io.IOException;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

/**
 * @Description 实体类生成sql语句
 **/

public class SQLTableUtil {

    @Value("spring.datasource.driverClassName")
    private String driverClassName;

    @Value("spring.datasource.url")
    private String url;

    @Value("spring.datasource.username")
    private String username;


    @Value("spring.datasource.password")
    private String password;


    public static Map<String, String> javaProperty2SqlColumnMap = new HashMap<>();

    final static String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    //指定连接数据库的url
    final static String DB_URL = "jdbc:mysql://127.0.0.1:3306/oa?characterEncoding=UTF8";
    //mysql用户
    final static String name = "root";
    //mysql密码
    final static String pwd = "";

    /*
     * 类型装换
     */
    static {
        javaProperty2SqlColumnMap.put("Integer", "INTEGER");
        javaProperty2SqlColumnMap.put("Short", "tinyint");
        javaProperty2SqlColumnMap.put("Long", "bigint");
        javaProperty2SqlColumnMap.put("BigDecimal", "decimal(19,2)");
        javaProperty2SqlColumnMap.put("Double", "double precision not null");
        javaProperty2SqlColumnMap.put("Float", "float");
        javaProperty2SqlColumnMap.put("Boolean", "bit");
        javaProperty2SqlColumnMap.put("Timestamp", "datetime");
        javaProperty2SqlColumnMap.put("String", "VARCHAR(255)");
        javaProperty2SqlColumnMap.put("long", "INTEGER(100)");
        javaProperty2SqlColumnMap.put("int", "INTEGER(100)");
        javaProperty2SqlColumnMap.put("boolean", "bit");
        javaProperty2SqlColumnMap.put("Date", "Date");
    }


    public static void main(String[] args) throws IOException {
        //String sql = createTable(User.class, "user");
        // CreatTables(sql);
    }


    /**
     * 返回sql语句
     *
     * @param obj       实体类
     * @param tableName 表名
     * @return
     * @throws IOException
     */
    public static <T> String createTable(Class<T> obj, String tableName) throws IOException {
        Field[] fields = null;
        fields = obj.getDeclaredFields();
        String param = null;
        String column = null;
        StringBuilder sb = null;
        sb = new StringBuilder(50);
        if (tableName == null || tableName.equals("")) {
            //未传表明默认用类名
            tableName = obj.getName();
            tableName = tableName.substring(tableName.lastIndexOf(".") + 1);
        }
        sb.append("create table ").append(tableName).append(" ( \r\n");
        boolean firstId = true;
        File file = null;
        for (Field f : fields) {
            column = f.getName();
            if (column.equals("serialVersionUID")) {
                continue;
            }
            param = f.getType().getSimpleName();
            sb.append(column);//一般第一个是主键
            sb.append(" ").append(javaProperty2SqlColumnMap.get(param)).append(" ");
            if (firstId) {//类型转换
                sb.append(" PRIMARY KEY ");
                firstId = false;
            }
            Annotation[] allAnnotations = f.getAnnotations();
            sb.append(",\n ");
        }
        String sql = null;
        sql = sb.toString();
        //去掉最后一个逗号
        int lastIndex = sql.lastIndexOf(",");
        sql = sql.substring(0, lastIndex) + sql.substring(lastIndex + 1);
        sql = sql.substring(0, sql.length() - 1) + " )ENGINE =INNODB DEFAULT  CHARSET= utf8;\r\n";
        System.out.println(sql);
        return sql;
    }

    /**
     * 执行sql语句
     *
     * @param sql
     * @return
     */
    public static void CreatTables(String sql) {
        Connection conn = null;
        Statement stmt = null;
        try {
            //注册jdbc驱动
            Class.forName(JDBC_DRIVER);
            //打开连接
            conn = DriverManager.getConnection(DB_URL, name, pwd);
            System.out.println(conn);
            //执行创建表
            stmt = conn.createStatement();

            if (0 == stmt.executeLargeUpdate(sql)) {
                System.out.println("成功创建表！");
            }
            else {
                System.out.println("创建表失败！");
            }
            stmt.close();
            conn.close();
        } catch (Exception e) {
            System.out.println("创建表失败！");
            e.printStackTrace();
        }
    }
}

